use master
select name, Database_id, compatibility_level,  recovery_model_desc, is_auto_update_stats_on, is_auto_create_stats_on from sys.databases


declare @sql as varchar(255);
set @sql = '
SELECT  
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]  
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]  
,[rows]  
,[data_compression_desc]  
,[index_id] as [IndexID_on_Table]
FROM sys.partitions  
INNER JOIN sys.objects  
ON sys.partitions.object_id = sys.objects.object_id  
WHERE data_compression > 0  
AND SCHEMA_NAME(sys.objects.schema_id) <> "SYS" 
ORDER BY SchemaName, ObjectName
'
exec  @sql
go
/**

sp_msforeachdb 'use ? SELECT  
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]  
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]  
,[rows]  
,[data_compression_desc]  
,[index_id] as [IndexID_on_Table]
FROM sys.partitions  
INNER JOIN sys.objects  
ON sys.partitions.object_id = sys.objects.object_id  
WHERE data_compression > 0  
AND SCHEMA_NAME(sys.objects.schema_id) <> "SYS" 
ORDER BY SchemaName, ObjectName'

 

 **/